﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;

namespace BLL
{
   public class ClsDuLieuMauBus
    {
      public ClsDuLieuMauInfo Info = new ClsDuLieuMauInfo();
       public DataTable SelectGK()
       {
           ClsDataAccess oConnection = new ClsDataAccess();
           string strSql = @"Select MaGK,TenGK FROM TTCHUNG WHERE TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "'";
           if (oConnection.ExecuteSelect(strSql))
           {
               oConnection.ODataTable.Columns.Add("STT");
               int i = 0;
               foreach (DataRow oDRow in oConnection.ODataTable.Rows)
               {
                   oDRow["STT"] = (i + 1);
                   i++;
               }
               return oConnection.ODataTable;
           }
           else return null;

        
       }
        public DataTable SelectAll()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.TenGK,DuLieuMau.MaMau,DuLieuMau.TenMau,DuLieuMau.ChieuSauLayMau,DuLieuMau.ThoiGianLayMau,DuLieuMau.TheTichMau,KieuLoDungMau
                            FROM DuLieuMau,TTCHUNG  
                            WHERE TTCHUNG.MaGK=DuLieuMau.MaGK
                            AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "'";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectOne()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.TenGK,DuLieuMau.MaMau,DuLieuMau.TenMau,DuLieuMau.ChieuSauLayMau,DuLieuMau.ThoiGianLayMau,DuLieuMau.TheTichMau,KieuLoDungMau
                            FROM DuLieuMau,TTCHUNG  
                            WHERE TTCHUNG.MaGK=DuLieuMau.MaGK
                            AND TTCHUNG.MaGK like '%" + Info.MaGK +
                            "%'AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "'";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertDuLieuMau '" + Info.MaGK+ "','" + Info.MaMau + "',N'" + Info.TenMau + "','" + Info.ChieuSauLayMau +"','" + Info.ThoiGianLayMau + "','" + Info.TheTichMau + "',N'" + Info.KieuLoDungMau + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateDuLieuMau '" + Info.MaGK + "','" + Info.MaMau + "',N'" + Info.TenMau + "','" + Info.ChieuSauLayMau + "','" + Info.ThoiGianLayMau + "','" + Info.TheTichMau + "',N'" + Info.KieuLoDungMau + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
        public bool Xoa()
        {
            string strSql = "EXECUTE DeleteDuLieuMau '" + Info.MaMau + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }
    }
}
